Database workload management, both human and automatic, addresses the problems of deciding how to manage the admission, scheduling, and execution of queries in a database system. For a data warehouse, the problems are especially challenging because jobs have uncertain resource requirements. For example, uncertainty creates particular difficulty in accurately detecting and handling “problem queries”—improperly functioning queries that may not complete and that may consume resources that could otherwise be used by properly functioning queries. Some commercial database systems supply tools for measuring resource consumption of a running query, but do not consider predicted resource usage or attempt to quantify progress or work remaining of a query. Database physical design advisors evaluate physical design search spaces, often with regard to specific query plans or atomic query plans, but do not consider a variety of runtime conditions such as resource availability and have comparisons are based completely on query optimizer cost estimates and not actual performance measurements. Traditional techniques for data workload management include usage of query progress indicators, query plan analysis, and workload execution management.
Query progress indicators are tools that attempt to quantify as a fraction the work that a running query has completed over the amount of work the query is likely to complete in total. Progress indicators generally use the size of intermediate results as a direct reflection of progress for collected statistics including output cardinality, average tuple size. Other techniques distinguish between classes of physical operators based on effectiveness of estimation methods, for example improving accuracy of estimation by excluding physical operators that perform nested iteration. Disadvantages of query progress indicators include: (1) reliance on accurate counts of the tuples processed by various operators, thus requiring developers to instrument the database core engine to count the tuples input and emitted from every operator; (2) because different types of operators process tuples at different rates, tuple-count based progress indicators require a model for dividing time to process a query among various types of operators wherein the model includes per-operator models of tuple processing rates as well as a model of mutual interaction of the per-operator models within processing of a single query; (3) query progress indicators fail to indicate whether query execution is progressing as expected; and (4) query progress indicators fail to evaluate impact of runtime conditions.
Workload execution management is implemented in many commercial database systems and includes various techniques and systems for dealing with problem queries. For example, HP-UX Workload Manager, IBM Query Patroller for DB2, SQLServer Query Governor, Teradata's Dynamic Workload Manager, and Oracle's Database Resource Manager all include functionality to control or address queries that exceed a limit on estimated row counts, processing time, or place a limit on the number of join operations that can appear in a plan. IBM's Query Patroller for DB2and Oracle's Database Resource Manager enable a system administrator to define usergroups to which a static priority and a share of system resources for each group are assigned. The higher the priority of a group, the more resources are allocated. However, the static prioritization is not associated with response time requirements or service level agreement (SLA) conformance. Similarly, SQLServer Query Governor prevents queries with estimated query costs that exceed a user-set upper cost limit from starting, as opposed to stopping the queries after reaching a predefined limit. These commercial attempts at managing long-running queries have required one or more of the following: (1) absolute limits on resource usage (for example, not admitting a query or stopping a query that exceeds a limit on estimated row counts, processing time, or placing a limit on the number of join operations that can appear in a plan), and (2) capability to obtain statistics such as actual input and output cardinalities. Obtaining such statistics can be prohibitively expensive, placing a great load on a running system.
Traditional query plan analysis techniques do not consider the impact of variable runtime conditions, such as resource availability, and do not systematically gather actual performance measurements over a variety of runtime conditions. Furthermore, traditional solutions focus on the selection of optimal query plans for a small range expected conditions, as opposed to the evaluation of database operators under a wide variety of actual conditions. For example, Harista et al. (U.S. Publication No. 2002/0046030) discloses a system that maps how well queries perform relative to one another in terms of estimated (expected) performance in ranges of the selectivity of a simple single-operator query with up to two parameters. Because the goal in Harista et al. is to reduce the number of plans in the query optimizer's plan search space, actual performance is not modeled and the impact of other conditions such as resource availability is not considered. Database regression tests may test the performance of individual operators, sometimes under specific resource availability conditions, but do not evaluate performance across a spectrum of conditions and do not consider performance as a continuous function across a spectrum of conditions. Database regression tests are used to evaluate performance—results are not stored nor later used to calculate an estimate for a specific query's performance under specific conditions.